package com.example.studyplanning.mapper;

import com.example.studyplanning.entity.Message;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface MessageMapper {
    //1.7展示我的留言：根据所选心得ID输出相关留言
    @Select("select message.*,user.userName,DATE_FORMAT(tipsTime,'%m-%d %H:%i') as date_time from message inner join user on message.userID=user.userID INNER JOIN tips on message.tipsID=tips.tipsID where tips.tipsID=#{tipsID}")
    List<Message> findAllMessage(Integer tipsID);

    //1.8留言：根据心得ID对心得进行留言
    @Insert("insert into message(messageID,messageContent,tipsID,userID) values(#{messageID},#{messageContent},#{tipsID},#{userID})")
    Integer insertMessage(Message message);

    //5.3展示我的留言，用户表，留言表，心得表三表连接，输入用户ID后展示对应信息
    @Select("select tips.*,user.userName,message.messageContent,DATE_FORMAT(message.messageTime,'%m-%d %H:%d') as date_time from message INNER JOIN user on message.userID=user.userID INNER JOIN tips on message.tipsID=tips.tipsID where user.userID=#{userID}")
    List<Message> SelectByID(Integer userID);

    //（后台）3.3留言删除
    @Delete("delete from message where messageID=#{messageID}")
    Integer DelByID(Integer messageID);

    //（后台）3.2 留言管理 根据心得ID，用户ID，留言ID查询相关信息
    @Select("select * from message where tipsID like '%${tipsID}' and userID like '%${userID}' and messageID like '%${messageID}'")
    List<Message> SelectMessage(@Param("tipsID")Integer tipsID,@Param("userID")Integer userID,@Param("messageID")Integer messageID);

    @Select("SELECT date_format(messageTime,\"%Y-%m-%d\") msgDate,count(*) as msgsum  FROM message GROUP BY date_format(messageTime,\"%Y-%m-%d\");")
    List<Message> CountDayMessage();

}
